-- @owner: danny20001210
-- @date: 2025-5-15
-- @testpoint:验证xmltype模块函数使用正确，success，部分用例合理报错
@conn PrimaryDbAmode;
drop table if exists test_xmltype_1087946;
drop table if exists test_xmltype_1087944;
drop table if exists test_xmltype_1087943;
drop table if exists test_xmltype_1087942;
drop table if exists test_xmltype_1087941;
drop table if exists test_xmltype_1087939;
drop table if exists test_xmltype_1087938;
drop table if exists test_xmltype_1087937;




create table test_xmltype_1087946(
ID int,
name varchar2(264),
data xmltype
);
Insert INTO test_xmltype_1087946
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind3=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;
--使用ExistsNode函数检查节点是否存在
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from test_xmltype_1087946 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="f"]') as a from test_xmltype_1087946 x;


create table test_xmltype_1087944(
ID int,
name varchar2(262),
data xmltype
);
Insert INTO test_xmltype_1087944
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind3=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;
--使用GetStringVal函数以字符串形式返回xml示例的值
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from test_xmltype_1087944 x;
create table test_xmltype_1087943(
ID int,
name varchar2(261),
data xmltype
);
Insert INTO test_xmltype_1087943
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind3=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;
--使用ExactValue函数提取满足标签的返回多个值
select extractvalue(x.data,'/collection/record/datafield/subfield') as A from test_xmltype_1087943 x;

create table test_xmltype_1087942(
ID int,
name varchar2(260),
data xmltype
);
Insert INTO test_xmltype_1087942
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind3=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;
--使用ExactValue函数提取满足标签的返回单个值
select extractvalue(x.data,'/collection/record/leader') as A from test_xmltype_1087942 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from test_xmltype_1087942 x;


create table test_xmltype_1087941(
ID int,
name varchar2(259),
data xmltype
);
Insert INTO test_xmltype_1087941
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind2=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;
--使用XmlSequence将提取的数据转为数组并通过unnest函数转为表
select unnest(XMLSequence(extract(x.data,'/collection/record/datafield/subfield'))) xmlseq from test_xmltype_1087941 x;

create table test_xmltype_1087939(
ID int,
name varchar2(257),
data xmltype
);
Insert INTO test_xmltype_1087939
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind2=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;
--使用exact提取标签
select extract(x.data,'/collection/record/datafield/subfield') xmlseq from test_xmltype_1087939 x;
create table test_xmltype_1087938(
ID int,
name varchar2(256),
data xmltype
);

--使用正确格式的xml文档
Insert INTO test_xmltype_1087938
VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind2=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')) ;

Insert INTO test_xmltype_1087938
VALUES (2,'test xml doc',xmltype('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
</record>
</collection>')) ;
--查看表
select *from test_xmltype_1087938;

create table test_xmltype_1087937(
ID int,
name varchar2(255),
data xmltype
);
---1.插入缺少最左尖括号数据（无对称<>）
Insert into test_xmltype_1087937 values(2,xmltype('b/>'));
Insert into test_xmltype_1087937 values(3,xmltype('xml version="1.0"?><person><one></one></person>'));
--2.查看表
select * from test_xmltype_1087937;
---插入缺少除最左尖括号的其他符号
--3.缺少最左边尖括号（有对称<>）
insert into test_xmltype_1087937 values(1,xmltype('a>testAA</a><b></b>'));
--4.缺少右边尖括号
Insert into test_xmltype_1087937 values(2,(xmltype'<b/'));
--5.缺少?
Insert into test_xmltype_1087937 values(3,xmltype('<xml version="1.0"?><person><one></one></person>'));
--6.缺少/
Insert into test_xmltype_1087937 values(4,xmltype('<test>abc<test><name>foo</name>'));
--查看表
select * from test_xmltype_1087937;
---7.插入缺少最左尖括号数据（无对称<>）
Insert into test_xmltype_1087937 values(2,xmltype.createxml('b/>'));
Insert into test_xmltype_1087937 values(3,xmltype.createxml('xml version="1.0"?><person><one></one></person>'));
--查看表
select * from test_xmltype_1087937;
---插入缺少除最左尖括号的其他符号
--8.插入缺少最左尖括号数据（有对称<>）
insert into test_xmltype_1087937 values(1,xmltype.createxml('a>testAA</a><b></b>'));
--9.缺少右边尖括号
Insert into test_xmltype_1087937 values(2,xmltype.create('<b/'));
--10.缺少?
Insert into test_xmltype_1087937 values(3,xmltype.create('<xml version="1.0"?><person><one></one></person>'));
--11.缺少/
Insert into test_xmltype_1087937 values(4,xmltype.create('<test>abc<test><name>foo</name>'));
--12.查看表
select * from test_xmltype_1087937;


drop table if exists test_xmltype_1087946;
drop table if exists test_xmltype_1087944;
drop table if exists test_xmltype_1087943;
drop table if exists test_xmltype_1087942;
drop table if exists test_xmltype_1087941;
drop table if exists test_xmltype_1087939;
drop table if exists test_xmltype_1087938;
drop table if exists test_xmltype_1087937;

